﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using SaleOffVn.BusinessEntities;

namespace SaleOffVn.DataAccess
{
    public class NewsDB
    {
        #region Public methods
        public static NewsInfo GetItemById(int newsId)
        {
            NewsInfo news = null;            
            using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("usp_InfoNam_NewsGetInfo", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.AddWithValue("@NewsID", newsId);
                    myConnection.Open();
                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.Read())
                        {
                            news = FillDataRecord(myReader);
                        }
                        myReader.Close();
                    }
                }
                myConnection.Close();
            }

            return news;
        }

        public static NewsCollection GetList(NewsInfo criteria)
        {
            NewsCollection tempList = null;

            using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("usp_InfoNam_NewsGetList", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.AddWithValue("@IsPublished", criteria.IsPublished);
                    
                    myConnection.Open();
                    using (SqlDataReader myReader = myCommand.ExecuteReader())
                    {
                        if (myReader.HasRows)
                        {
                            tempList = new NewsCollection();
                            while (myReader.Read())
                            {
                                tempList.Add(FillDataRecord(myReader));
                            }
                        }
                        myReader.Close();
                    }
                }
            }
            return tempList;
        }

        /// <summary>Insert an News to database.</summary>
        /// <param name="account">The News to Insert.</param>
        /// <returns></returns>
        public static int Add(NewsInfo news)
        {
            int iIDNews = 0;
            using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("usp_InfoNam_NewsAdd", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;

                    myCommand.Parameters.AddWithValue("@TitleName", news.TitleName);
                    myCommand.Parameters.AddWithValue("@ShortDescription", news.ShortDescription);
                    myCommand.Parameters.AddWithValue("@FullDescription", news.FullDescription);
                    myCommand.Parameters.AddWithValue("@PathThumbnail", news.PathThumbnail);
                    myCommand.Parameters.AddWithValue("@IsPublished", news.IsPublished);
                    myCommand.Parameters.AddWithValue("@CreatedOn", news.CreatedOn);
                    myCommand.Parameters.AddWithValue("@UpdatedOn", news.UpdatedOn);
                    myConnection.Open();
                    iIDNews = Int32.Parse(myCommand.ExecuteScalar().ToString());
                    if (iIDNews <= 0)
                    {
                        throw new DBConcurrencyException("The News cannot be added");
                    }
                }
                myConnection.Close();
            }
            return iIDNews;
        }

        /// <summary>Update an News to database.</summary>
        /// <param name="account">The News to update.</param>
        /// <returns></returns>
        public static int Update(NewsInfo news)
        {
            int result = 0;
            using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("usp_InfoNam_NewsUpdate", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;

                    myCommand.Parameters.AddWithValue("@NewsID", news.NewsID);
                    myCommand.Parameters.AddWithValue("@TitleName", news.TitleName);
                    myCommand.Parameters.AddWithValue("@ShortDescription", news.ShortDescription);
                    myCommand.Parameters.AddWithValue("@FullDescription", news.FullDescription);
                    myCommand.Parameters.AddWithValue("@PathThumbnail", news.PathThumbnail);
                    myCommand.Parameters.AddWithValue("@IsPublished", news.IsPublished);
                    myCommand.Parameters.AddWithValue("@UpdatedOn", news.UpdatedOn);
                    myConnection.Open();
                    result = Int32.Parse(myCommand.ExecuteScalar().ToString());
                    if (result <= 0)
                    {
                        throw new DBConcurrencyException("The News cannot be updated");
                    }
                }
                myConnection.Close();
            }
            return result;
        }


        /// <summary>Delete an News from database.</summary>
        /// <param name="accountId">The ID of the News to delete.</param>
        /// <returns></returns>
        public static void Delete(int newsID)
        {            
            using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("usp_InfoNam_NewsDelete", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.AddWithValue("@NewsID", newsID);
                    myConnection.Open();
                    myCommand.ExecuteNonQuery();                    
                }
                myConnection.Close();
            }
        }

        public static DataTable GetAllNews(int isPublished)
        {
            DataTable resultdt = new DataTable();      
            int iAction = -1;
            if (isPublished == -1)
                iAction = 0;    //Get all news (include published or not)
            else
                iAction = 1;        //Get all news according to isPublished condition
            using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString))
            {
                using (SqlCommand myCommand = new SqlCommand("usp_InfoNam_NewsGetList", myConnection))
                {
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.AddWithValue("@ACTION", iAction);
                    myCommand.Parameters.AddWithValue("@IsPublished", isPublished);
                    myConnection.Open();
                    using (SqlDataAdapter myDataAdapter = new SqlDataAdapter(myCommand))                        
                    {
                        myDataAdapter.Fill(resultdt);                        
                    }
                    myConnection.Close();
                }                                
            }
            return resultdt;
        }

        public static NewsInfo GetPreviousNews(int iCurrNewsID)
        {
            NewsInfo newsinfo = null;
            string strSQL = "SELECT top 1 * FROM dbo.InfoNam_News WHERE NewsID < " + iCurrNewsID + " AND IsPublished = 1 ORDER BY CreatedOn DESC";
            SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString);
            SqlCommand myCommand = new SqlCommand();            
            myConnection.Open();
            myCommand.CommandText = strSQL;
            myCommand.CommandTimeout = 300;
            myCommand.Connection = myConnection;
            SqlDataReader myReader = myCommand.ExecuteReader();
            if (myReader.Read())
            {
                newsinfo = FillDataRecord(myReader);
            }
            myReader.Close();
            myConnection.Close();            
            return newsinfo;
        }

        public static NewsInfo GetNextNews(int iCurrNewsID)
        {
            NewsInfo newsinfo = null;
            string strSQL = "SELECT top 1 * FROM dbo.InfoNam_News WHERE NewsID > " + iCurrNewsID + " AND IsPublished = 1 ORDER BY CreatedOn ASC";
            SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString);
            SqlCommand myCommand = new SqlCommand();
            myConnection.Open();
            myCommand.CommandText = strSQL;
            myCommand.CommandTimeout = 300;
            myCommand.Connection = myConnection;
            SqlDataReader myReader = myCommand.ExecuteReader();
            if (myReader.Read())
            {
                newsinfo = FillDataRecord(myReader);
            }
            myReader.Close();
            myConnection.Close();
            return newsinfo;
        }

        #endregion Public methods

        #region Private methods
        private static NewsInfo FillDataRecord(IDataRecord myDataRecord)
        {
            NewsInfo news = new NewsInfo();

            news.NewsID = myDataRecord.GetInt32(myDataRecord.GetOrdinal("NewsID"));
            news.TitleName = myDataRecord.GetString(myDataRecord.GetOrdinal("TitleName"));
            news.ShortDescription = myDataRecord.GetString(myDataRecord.GetOrdinal("ShortDescription"));
            news.FullDescription = myDataRecord.IsDBNull(myDataRecord.GetOrdinal("FullDescription")) ? "" : myDataRecord.GetString(myDataRecord.GetOrdinal("FullDescription"));
            news.PathThumbnail = myDataRecord.IsDBNull(myDataRecord.GetOrdinal("PathThumbnail")) ? "" : myDataRecord.GetString(myDataRecord.GetOrdinal("PathThumbnail"));
            news.IsPublished = myDataRecord.GetBoolean(myDataRecord.GetOrdinal("IsPublished"));
            news.CreatedOn = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("CreatedOn"));
            news.UpdatedOn = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("UpdatedOn"));
            return news;
        }
        #endregion Private methods
    }
}
